About adding and removing fields

When you add data to a PivotTable list, you specify the database to take the data from and the subset of that data to make available as the source data for the PivotTable list. From this source data, you can select fields for initial display in the PivotTable list.

Use the field list to view the list of fieldsets and fields that are available from the source data, and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed. You can scroll and click the expand indicators ( Plus box or Minus box boxes) next to the fieldsets to show or hide fields in the field list.

If your PivotTable list is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas. Other fields, listed under Totals, can be used only as data fields. Fields in a fieldset cannot be displayed in two different drop areas.

Use detail fields to display all of the available detail data and see what data is available to be summarized. When you add fields to the detail area, you display all of the detail records from the source data for the fields. Field names become column labels, and the detail records are displayed in rows below the column labels.

Example of detail fields

Detail fields

Detail data

If your PivotTable list is not based on source data from an OLAP database, you can add a calculated detail field that uses an expression to calculate detail values.

Use row and column fields to summarize and compare data. These fields display the unique items of data within a field down rows or across columns. The cell at each row and column intersection summarizes the data for an item.

Example of row and column fields

Column field

Column field item

Row field

Row field items

Data fields provide the values to be summarized in the PivotTable list. When you add a field to the data area, the values from the field are summarized for the row and column field items.

Example of a data field

Data field

Summarized data

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Region filter field, you can have the PivotTable list display and calculate data for the regions you select.

Example of a filter field

Filter field

Filter field item

Summary for the selected region

You can create new data fields by adding total fields to the PivotTable list. Total fields provide summarized data for use as data fields. For example, if your source data includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or a Count of Sales total field to calculate how many units were sold.

You can also add a calculated total field that uses an expression to calculate total values.

To eliminate data that you don't want to see or summarize, you can remove fields from the PivotTable list layout. Removing a field does not remove it from the available source data. A field that you remove is no longer displayed in the PivotTable list, but it remains available in the field list for viewing.

After you add a field to a PivotTable list, you can move it to other areas. When you move a field, you change the layout of the PivotTable list, and you change the data that's displayed and the summaries that are calculated.

Filter settings are retained when you remove a field. If you remove a field and later add the field back to the layout, the same items are again hidden.

You can view information about fields in a PivotTable list, including information about the field in the source database from which the data is taken and the type of data that the source database supplies for the field.

The features for adding fields and creating summaries are also available in the browser, unless you restrict the user's access. If you don't want users to change the fields you've designated as row and column fields, you can prevent row and column fields from being added and moved in the browser. You can also restrict access to detail so that users can't move fields to the detail area, and restrict access to filtering so that users can't move fields to the filter area.